Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# MODEL

The MODEL command enters a completely new specification into a new or existing model object. When the model already has a specification, Oracle OLAP overwrites it. In order to use MODEL to assign an model specification to a model object, the definition must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

An alternative to a MODEL statement is an EDIT MODEL statement, which is available only in OLAP Worksheet. An EDIT MODEL statement opens an Edit window in which you can add, delete, or change the specification for a model object.

Adding a specification to a model object is just one step in modeling data which is discussed in Chapter 4, "Models".

Syntax

MODEL specification

Arguments

specification

  A multiline text expression that contains one or more of the following OLAP DML statements:

    Assignment statement (SET)
    DIMENSION (in models)
    INCLUDE

  The maximum number of lines you can have in a model is 4,000. Each statement is a line of the multiline text expression. When coding an ALLOCMAP statement at the command line level, separate statements with newline delimiters (`\n`), or use JOINLINES.

  For a discussion of designing a model specification, see "Model Specification".

Notes

Model Specification

The model specification consists of the following OLAP DML statements:

1. One of the following:

   - Exactly one INCLUDE statement that specifies the name of another model to include. See "Nesting Models" for more information.

   - One or more DIMENSION (in models) statements coded following the "Guidelines for Writing DIMENSION Statements in a Model".

     Note: When a model contains an INCLUDE statement, then it cannot contain any DIMENSION statements. However, the model referenced in the INCLUDE statement or the root model in a hierarchy must contain the DIMENSION statements needed by the parent model(s).

2. One or more SET commands or equations written following the "Rules for Equations in Models". The maximum number of lines you can have in a model is 4,000.

   See also: "Dimension Status and Model Equations" for information on how Oracle OLAP processes equations in a model.

3. A final END statement that indicates the end of the model specification. (Omit when coding the specification in an Edit window of the OLAP Worksheet.)

MODEL Statement in an Aggregation Specification

Within an aggmap, you can use a special MODEL statement to execute a predefined model. (See MODEL (in an aggregation) for more information.

Methods of Calculating Data Within a Variable

Both models and aggmap objects calculate data values within a variable based on relationships among dimension members. When a parent-child relationship exists among dimension members (that is, the dimension has a hierarchical structure) and all aggregate values can be calculated using the same method, then you can use a RELATION (for aggregation) statement within an aggregation specification to calculate the values. However, when the dimension is not hierarchical and different equations are needed to calculate the values, then you must define a model. You can use a MODEL (in an aggregation) to execute the MODEL within an aggregation specification or you can run a model at the command line using the syntax shown in "Running a Model".

Deleting a Model Specification

You can remove the specification of a model without deleting the model definition. Consider the model with a CONSIDER statement. Then issue a MODEL statement and enter the word END as the model specification.

Examples

Example 17-9 Model Specified in a Program

In the following example, a simple model is created (or overwritten) in a program called `myprog`. The first line in the program defines or considers the model. The second line contains the MODEL statement, which provides the lines of the model.

This model calculates the line items in a budget. The model equations are based on a `line` dimension.

    DEFINE myprog PROGRAM
    PROGRAM
    IF NOT EXISTS('myModel')
      THEN DEFINE myModel
      ELSE CONSIDER myModel
    MODEL JOINLINES(-
      'DIMENSION line month' -
      'Opr.Income = Gross.Margin - Marketing' -
      'Gross.Margin = Revenue - Cogs' -
      'Revenue = LAG(Revenue, 1, month) * 1.02' -
      'Cogs = LAG(Cogs, 1, MONTH) * 1.01' -
      'Marketing = LAG(Opr.Income, 1, month) * 0.20' -
      'END')
    END

Example 17-10 Model from an Input File

This example presents the text of the same simple model, but it is stored in an ASCII disk file called `budget.txt`.

    DEFINE income.budget MODEL
    MODEL
    DIMENSION line month
    Opr.Income = Gross.Margin - Marketing
    Gross.Margin = Revenue - Cogs
    Revenue = LAG(Revenue, 1, month) * 1.02
    Cogs = LAG(Cogs, 1, month) * 1.01
    Marketing = LAG(Opr.Income, 1, month) * 0.20
    END

To include the `income.budget` model in your analytic workspace, execute the following statement in which `myinpfiles` is a directory object.

    INFILE 'myinpfiles/budget.txt'

Example 17-11 Creating a Model

Suppose that you define a model, called `income.calc`, that calculates line items in the income statement.

    define income.calc model
    ld Calculate line items in income statement

After defining the model, you can use a `MODEL` statement or the OLAP Worksheet editor to enter the specification for the model. A model specification can contain `DIMENSION` commands, assignment statements and comments. All the `DIMENSION` commands must come before the first equation. For the current example, you can specify the lines shown in the following model.

    DEFINE INCOME.CALC MODEL
    LD Calculate line items in income statement
    MODEL
    DIMENSION line
    net.income = opr.income - taxes
    opr.income = gross.margin - (marketing + selling + r.d)
    gross.margin = revenue - cogs
    END

When you write the equations in a model, you can place them in any order. When you compile the model, either by issuing a `COMPILE` statement or by running the model, the order in which the model equations are solved is determined. When the calculated results of one equation are used as input to another equation, then the equations are solved in the order in which they are needed.

To run the `income.calc` model and use `actual` as the solution variable, you execute the following statement.

    income.calc actual

When the solution variable has dimensions other than the dimensions on which model equations are based, then a loop is performed automatically over the current status list of each of those dimensions. For example, `actual` is dimensioned by `month` and `division`, as well as by `line`. When `division` is limited to `ALL`, and `month` is limited to `OCT96` to `DEC96`, then the `income.calc` model is solved for the three months in the status for each of the divisions.

Example 17-12 Building a Scenario Model

Suppose, for example, you want to calculate profit figures based on optimistic, pessimistic, and best-guess revenue figures for each division. The steps for building this scenario model are explained in the following example.

You can call the scenario dimension `scenario` and give it values that represent the scenarios you want to calculate.

These commands give `scenario` the values `optimistic`, `pessimistic` and `bestguess`.

    DEFINE scenario DIMENSION TEXT
    LD Names of scenarios
    MAINTAIN scenario ADD optimistic pessimistic bestguess

These commands create a variable named `plan` dimensioned by three other dimensions (`month`, `line`, and `division`) in addition to the `scenario` dimension.

    DEFINE plan DECIMAL <month line division scenario>
    LD Scenarios for financials

For this example, you need to enter input data, such as revenue and cost of goods sold, into the `plan` variable.

For the best-guess data, you can use the data in the `budget` variable. Limit the `line` dimension to the input line items, and then copy the `budget` data into the `plan` variable.

    LIMIT scenario TO 'BESTGUESS'
    LIMIT line TO 'REVENUE' 'COGS' 'MARKETING' 'SELLING' 'R.D'
    plan = budget

You might want to base the optimistic and pessimistic data on the best-guess data. For example, optimistic data might be fifteen percent higher than best-guess data, and pessimistic data might be twelve percent less than best-guess data. With `line` still limited to the input line items, execute the following commands.

    plan(scenario 'OPTIMISTIC') = 1.15 * plan(scenario 'BESTGUESS')
    plan(scenario 'PESSIMISTIC') = .88 * plan(scenario 'BESTGUESS')

The final step in building a scenario model is to write a model that calculates results based on input data. The model might contain calculations very similar to those in the `budget.calc` model shown earlier in this chapter.

You can use the same equations for each scenario or you can use different equations. For example, you might want to calculate the cost of goods sold and use a different constant factor in the calculation for each scenario. To use a different constant factor for each scenario, you can define a variable dimensioned by `scenario` and place the appropriate values in the variable. When the name of your variable is `cogsval`, then your model might include the following equation for calculating the `cogs` line item.

    cogs = cogsval * revenue

By using variables dimensioned by `scenario`, you can introduce a great deal of flexibility into your scenario model.

Similarly, you might want to use a different constant factor for each division. You can define a variable dimensioned by `division` to hold the values for each division. For example, when labor costs vary from division to division, then you might dimension `cogsval` by `division` as well as by `scenario`.

When you run your model, you specify `plan` as the solution variable. For example, when your model is called `scenario.calc`, then you solve the model with this statement.

    scenario.calc plan

A loop is performed automatically over the current status list of each of the dimensions of `plan`. Therefore, when the `scenario` dimension is limited to `ALL` when you run the `scenario.calc` model, then the model is solved for all three scenarios: `optimistic`, `pessimistic`, and `bestguess`.

## DIMENSION (in models)

The DIMENSION statement at the beginning of a model tells Oracle OLAP the names of one or more dimensions to which the model assigns data or to which it refers in dimension-based equations. A dimension-based equation assigns the results of a calculation to a target that is represented by one or more values of a dimension.

Syntax

DIMENSION dimension1 [, dimensionN]

Arguments

dimension

  One or more dimensions, including base dimensions of composites, on which model equations are based. You can specify the name of a dimension surrogate instead of the dimension for which is a surrogate. You can then use the values of the surrogate instead of the values of the dimension.

Notes

Dimension-Based Equations in Models

When an equation (SET) assigns data to a dimension value or refers to dimension values in its calculations, it is called a dimension-based equation. Note that a dimension-based equation does not need to refer to the dimension itself, but only to the values of the dimension. Therefore, when the model contains any dimension-based equations, you must specify the name of each of these dimensions in a DIMENSION statement at the beginning of the model. This allows Oracle OLAP to determine the dimension to which each dimension value belongs. You can specify the name of a dimension surrogate instead of the dimension for which it is a surrogate. You can then use the values of the surrogate instead of the values of the dimension.

In addition, when a model contains any dimension-based equations, you must supply the name of a solution variable when you run the model. The solution variable is both the source and the target of data for the model. It holds the input data used in dimension-based calculations, and Oracle OLAP stores the calculation results in designated values of the solution variable. The solution variable is generally dimensioned by all the dimensions on which the model equations are based. For example, in a financial application, the model might be based on the `line` dimension, and the solution variable might be `actual`, which has `line` as one of its dimensions.

Dimension-based equations provide flexibility in modeling. Since you do not need to specify the modeling variable until you solve a model, you can run the same model with different solution variables. For example, you might run the same model with the `actual` variable, with a "best case" budget variable, and with a "worst case" budget variable.

A dimension must be specified in a DIMENSION statement when a dimension-based equation refers to a value of the dimension either as a source of the data used in the calculation or as the target to which the results will be assigned. In the following example, `Gross.Margin`, `Revenue`, and `Cogs` are values of the `line` dimension, so `line` is specified in a DIMENSION statement.

    DIMENSION line
    Gross.Margin = Revenue - Cogs

Dimension is a Function Argument

A dimension must be specified in a DIMENSION statement when the dimension is an argument to a function that uses a dimension value as its data source. In the following example, `month` must be specified in a DIMENSION statement.

    DIMENSION line, month
    Revenue = lag(Revenue, 1, month) * 1.05

The writer of the preceding model expects to use a solution variable that is dimensioned by `line` and `month`. Therefore, when the model is run, the LAG function will operate on a solution variable that has the specified time dimension (`month`) as one of its dimensions. However, since the model compiler cannot anticipate the time dimension of the solution variable, you must specify it in a DIMENSION statement. When you fail to include `month` in a DIMENSION statement, an error occurs when you attempt to compile the model.

In a function that operates on time-series data (such as MOVINGTOTAL or LAG), the dimension argument is optional when the dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. For example, you can omit `month` from the LAG function in the preceding example. However, you must still specify the appropriate time dimension in a DIMENSION statement.

Solution Variable

When you run a model that contains dimension-based equations, you specify a solution variable, which is both the source and the target of data for the model. The solution variable is generally dimensioned by all the dimensions that are listed in the DIMENSION commands used in the model. Or, when a solution variable is dimensioned by a composite, the DIMENSION commands can list base dimensions of the composite. The DIMENSION commands can be explicit in the model or inherited through an included model. See "Incompatibility with INCLUDE".

Working with Composites

When you expect to run a model with a solution variable that has a composite in its dimension list, you can specify a base dimension of the composite in a DIMENSION statement. Your model equations will assign results to values of the base dimension. Oracle OLAP automatically creates any new values that are needed in the composite.

Multiple DIMENSION Commands

You can include a separate DIMENSION statement for every dimension referred to or used in dimension-based equations, or you can specify all the dimensions in a single DIMENSION statement.

Location of Commands

You must place all the DIMENSION commands at the beginning of the model, before any equations.

Incompatibility with INCLUDE

When a model contains an INCLUDE statement, it cannot contain any DIMENSION commands. The INCLUDE statement specifies another model to include in the current model. In this case, the current model inherits its DIMENSION commands, if any, from the included model. For more information in including models, see INCLUDE.

Inherited DIMENSION commands must satisfy all the requirements specified for explicit DIMENSION commands. See "Guidelines for Writing DIMENSION Statements in a Model".

Dimension Order

When more than one dimension is specified by the DIMENSION commands in a model, the order in which the dimensions are listed is important:

- When a model equation contains a name that might be a dimension value, Oracle OLAP searches through the dimensions that appear in the model's explicit or inherited DIMENSION commands, in the order you list the dimensions, to determine whether the name matches a dimension value of a listed dimension. The search concludes as soon as a match is found. Therefore, when two or more listed dimensions have a dimension value with the same name, Oracle OLAP assumes that the value belongs to the dimension specified earliest in a DIMENSION statement. When the name does not match a value of a listed dimension, Oracle OLAP then searches through the variables in the attached workspaces to find a match.

- When model equations assign results to values of a target dimension, Oracle OLAP constructs code that will loop over the values of the other, non-target, dimensions listed in the DIMENSION commands. The non-target dimension listed first in the DIMENSION commands is treated as the slowest-varying dimension. For example, when MONTH is the first non-target dimension listed in a DIMENSION statement and DIVISION is the second, Oracle OLAP loops through all the divisions for the first month, then all the divisions for the second month, and so on.

Guidelines for Writing DIMENSION Statements in a Model

When you write `DIMENSION` statements, you should keep these points in mind:

- In the `DIMENSION` statements, you must list the names of all the dimensions on which model equations are based. In the following example, `gross.margin`, `revenue`, and `cogs` are values of the `line` dimension, so `line` is specified in a `DIMENSION` statement.

      DIMENSION line
      gross.margin = revenue - cogs

- `DIMENSION` statements must also list any dimension that is an argument to a function that refers to a dimension value. In the following example, `month` must be specified in a `DIMENSION` statement.

      DIMENSION line, month
      revenue = LAG(revenue, 1, month) * 1.05

- When a model equation assigns results to a dimension value, then code is constructed that loops over the values of any of the other nontarget dimensions listed in the `DIMENSION` statements. The nontarget dimension listed first in the `DIMENSION` statements is treated as the slowest-varying dimension.

- A model executes most efficiently when you observe the following guidelines for coordinating the dimensions in `DIMENSION` statements and the dimensions of the solution variable:

  - List the target dimension of the model as the first dimension in the `DIMENSION` statements and as the last dimension in the definition of the solution variable.

  - In `DIMENSION` statements, list the nontarget dimensions in the reverse order of their appearance in the definition of the solution variable. This means that the fastest-varying and slowest-varying nontarget dimensions are in the same order in the model and in the solution variable.

- When the solution variable has dimensions that are not used or referred to in model equations, then do not include them in `DIMENSION` statements.

- When your analytic workspace contains a variable whose name is the same as a dimension value, or when the same dimension value exists in two different dimensions, then there could be ambiguities in your model equations. Since you can use a variable and a dimension value in exactly the same way in a model equation, a name might be the name of a variable, or it might be a value of any dimension in your analytic workspace.

- Your `DIMENSION` statements are used to determine whether each name reference in an assignment statement is a variable or a dimension value. "Compiling Models" explains how the name references are resolved.

  See Also: Chapter 4, "Models", SET, and MODEL for information on: Entering statements in a model How to refer to values of dimensions Explanation of how Oracle OLAP constructs code from the statements Explanation of how Oracle OLAP handles the situation in which the solution variable has more dimensions or fewer dimensions than are listed in DIMENSION commands

Examples

Example 17-13 Simplified Model for Budget Estimates

The following statements define a simplified model that estimates budget values for the items on an income statement.

    DEFINE income.budget MODEL
    LD Model for estimating budget line items
    MODEL
    dimension line, month
    Revenue = 1.05 * LAG(Revenue 1 month)
    Gross.Margin = Revenue - Cogs
    Opr.Income = Gross.Margin - (Marketing + Selling + R.D)
    Net.Income = Opr.Income - Taxes
    END

The model equations are based on the `line` dimension, so `line` is specified in the DIMENSION statement. The dimension `month` is the time dimension in the LAG function that operates on REVENUE values, so `month` is also specified in the DIMENSION statement.

When you run the model, Oracle OLAP loops over the values in the current status of the `month` dimension.

## INCLUDE

The INCLUDE statement includes one model within another model. You can use the INCLUDE statement only within models.

Use INCLUDE to create modular models by placing equations that are common to several models, in a separate model for inclusion on other models as needed. The INCLUDE statement also facilitates what-if analyses. An experimental model can draw equations from a base model and selectively replace them with new equations

Syntax

INCLUDE model

Arguments

model

  The name of a model to include in the current model. The current model is referred to as the parent model. The model that you include is referred to as the base model.

Notes

Guidelines for Coding INCLUDE Statements in a Model

Follow these guidelines for using INCLUDE statements in models:

- A model can contain only one INCLUDE statement.

- The INCLUDE statement must be before any equations in the model.

- A model that contains an INCLUDE statement cannot contain any DIMENSION (in models) statements.

How to Nest Models

You can nest models by placing an INCLUDE statement in a base model. For example, model `myModel1` can include model `myModel2`, and model `myModel2` can include model `myModel3`. The nested models form a hierarchy. In this example, `myModel1` is at the top of the hierarchy, and `myModel3` is at the root. A base model cannot include a model at a higher level in the hierarchy. In the preceding example, `myModel2` cannot include `myModel1`, and `myModel3` cannot include `myModel1` or `myModel2`.

Dependencies Among Equations

When compiling a model that contains an INCLUDE statement, the compiler considers the dependencies among the equations from all the included models when it orders and blocks the equations. Therefore, when you run the `MODEL.COMPRPT` program to examine the results of the compilation or when you set the MODTRACE option to `YES` before running the parent model, you might find that equations from different levels in the hierarchy of included models are interspersed. See Example 17-15, "Producing a Compilation Report".

When the compiler finds no dependencies among the equations from the included models, it executes the equations in the root model first and the equations in the parent model last.

Compiling a Parent Model

When you compile a parent model, the compiler will compile all the base models under it in the included hierarchy when compiled code does not already exist. When the compiler detects an error in an included model, neither it nor any model above it in the hierarchy is compiled. When the root model of the included hierarchy contains an error, the higher-level models are unable to inherit any DIMENSION (in models) statements from the root model. In this case, the compiler might report an error in a parent model when the source of the error is actually in the root model. For example, the compiler might report that a target dimension value does not exist in any attached analytic workspace. On the other hand, when the compiler detects an error in a parent model but finds no errors in the included models, the included models are compiled even though the parent model is not.

Masking Equations

To support what-if analyses, Oracle OLAP allows equations in a model to mask previous equations. The previous equations can come from the same model or from included models. A masked equation is not executed. When you run the `MODEL.COMPRPT` program after compiling the model, you will see that the masked equation is not shown in the report on the compiled model.

Masking can take place when an equation assigns a value to a variable or dimension value that is also the target of a previous equation. The masking rules are as follows:

- When the target in the earlier equation is qualified exactly the same as the target in the later equation, the earlier equation is masked and the later equation is executed. The following example illustrates two equations with targets that are identically qualified.

      Equation from a base model:      BUDGET(LINE REVENUE) = 5000
      Equation from the parent model:  BUDGET(LINE REVENUE) = 3500

  In this example, the equation from the base model is masked and the equation from the parent model is executed.

- When the target in the earlier equation is more qualified than the target in the later equation, the earlier equation is masked. The later equation is executed.

  The target that is more qualified is the one that will affect the fewest dimension values. Consider the following equations from a base model and a parent model.

      Equation from a base model:      BUDGET(LINE REVENUE) = 2500
      Equation from the parent model:  BUDGET = 4000

  The equation from the base model is more qualified because it assigns data only for the REVENUE value of the LINE dimension. The equation from the parent model assigns data to all the values of the LINE dimension. In this example, the equation from the base model is masked and the equation from the parent model is executed.

- When the target in the earlier equation is less qualified than the target in the later equation, no masking takes place. Both equations are executed.

  Consider the following equations from a base model and a parent model.

      Equation from a base model:      BUDGET = LAG(ACTUAL, 1, MONTH)
      Equation from the parent model:  BUDGET(LINE REVENUE) = 6500
      Equation from the parent model:  BUDGET(LINE COGS) = 4000

  The equation from the base model assigns data to all the values of the LINE dimension. The equations from the parent model are more qualified because each assigns data only for a single value of the LINE dimension. In this example, the equation from the base model is executed first, and then the equations from the parent model are executed.

  This functionality enables you to assign a large number of values with one equation and use subsequent equations to replace or test individual values.

- When the target in the earlier equation is qualified differently from the target in the later equation, no masking takes place. Both equations are executed. In the following example, both equations are executed.

      Equation from a base model:      BUDGET(LINE REVENUE) = 5000
      Equation from the parent model:  BUDGET(LINE COGS) = 4500

Examples

Example 17-14 Including a Model

This example shows a parent model named `income.plan` that includes a base model named `base.lines`.

    DEFINE income.plan MODEL
    MODEL
    INCLUDE base.lines
    revenue = LAG(revenue, 1, month) * 1.02
    cogs = LAG(cogs, 1, month) * 1.01
    taxes = 0.3 * opr.income
    END

    DEFINE BASE.LINES MODEL
    MODEL
    DIMENSION line month
    net.income = opr.income - taxes
    opr.income = gross.margin - marketing
    gross.margin = revenue - cogs
    END

Example 17-15 Producing a Compilation Report

The following statements compile the parent model and produce a compilation report.

    COMPILE income.plan
    MODEL.COMPRPT income.plan

These statements produce the following output.

    MODEL INCOME.PLAN <LINE MONTH>
                          BLOCK 1 (SIMPLE)
    INCOME.PLAN     2:    revenue = lag(revenue, 1, month) * 1.02
    INCOME.PLAN     3:    cogs = lag(cogs, 1, month) * 1.01
    BASE.LINES      4:    gross.margin = revenue - cogs
    BASE.LINES      3:    opr.income = gross.margin - marketing
    INCOME.PLAN     4:    taxes = 0.3 * opr.income
    BASE.LINES      2:    net.income = opr.income - taxes
                          END BLOCK 1